package socratesteam.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import socratesteam.model.common.Fecha;
import socratesteam.model.dominio.Aviso;
import socratesteam.model.dominio.Estado;
import socratesteam.model.dominio.Tipo;
import socratesteam.model.dominio.Usuario;
import socratesteam.model.excepcion.DAOExcepcion;
import socratesteam.model.util.ConexionBD;

public class AvisoDAO extends BaseDAO {

	private static final long serialVersionUID = 1644061774572158929L;

	private Connection oConnection = null;
	private PreparedStatement oStatement = null;
	private ResultSet oSet = null;
	private String query = "";
	
	public Aviso insertar(Aviso oAviso) throws DAOExcepcion {
		String query = "INSERT INTO TAB_AVISO " +
				//"(N_CODIGO_USUARIO," +
				//"N_ESTADO," +
				"C_TITULO," +
				"C_AVISO, " +
				"C_UBICACION, " +
				"D_FECHA_INICIO," +
				"D_FECHA_FIN," +
				"D_FECHA_CREACION) " +
				"VALUES (?, ?, ?, ?, ?, CURDATE())";

		try {
			oConnection = ConexionBD.obtenerConexion();
			oStatement = oConnection.prepareStatement(query);
			//oStatement.setInt(1, oAviso.getUsuario().getCodigo());
			oStatement.setInt(2, oAviso.getEstado().getCodigo());
			oStatement.setString(3, oAviso.getTitulo());
			oStatement.setString(4, oAviso.getAviso());
			oStatement.setInt(5, oAviso.getUbicacion().getCodigo());
			oStatement.setDate(6, Fecha.utilToSql(oAviso.getFechaInicio()));
			oStatement.setDate(7, Fecha.utilToSql(oAviso.getFechaFin()));
			
			int i = oStatement.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			query = "SELECT LAST_INSERT_ID()";
			oStatement = oConnection.prepareStatement(query);
			oSet = oStatement.executeQuery();
			if (oSet.next()) {
				oAviso.setCodigo(oSet.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(oSet);
			this.cerrarStatement(oStatement);
			this.cerrarConexion(oConnection);
		}
		return oAviso;
	}
	
	public List<Aviso> mostrarAviso() throws DAOExcepcion {
		List<Aviso> lista = new ArrayList<Aviso>();
		Aviso o;
		try {
			oConnection = ConexionBD.obtenerConexion();
			String query = "SELECT C_TITULO, C_AVISO FROM TAB_AVISO WHERE N_ESTADO = 1 AND CURDATE() BETWEEN D_FECHA_INICIO AND D_FECHA_FIN";
			oStatement = oConnection.prepareStatement(query);
			oSet = oStatement.executeQuery();
			while (oSet.next()) {
				o = new Aviso();
				o.setTitulo(oSet.getString(1));
				o.setAviso(oSet.getString(2));
				lista.add(o);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(oSet);
			this.cerrarStatement(oStatement);
			this.cerrarConexion(oConnection);
		}
		return lista;
	}
	
	public List<Aviso> listarAvisos() throws DAOExcepcion {
		List<Aviso> lista = new ArrayList<Aviso>();
		Aviso o;
		try {
			query = "SELECT A.N_CODIGO, A.N_CODIGO_USUARIO, " +
					"A.N_ESTADO, ES.C_NOMBRE_CORTO AS C_TD_NOMBRE_CORTO, ES.C_NOMBRE_LARGO AS C_TD_NOMBRE_LARGO, " +
					"A.N_UBICACION, TI.C_NOMBRE_CORTO AS C_TD_NOMBRE_CORTO, TI.C_NOMBRE_LARGO AS C_TD_NOMBRE_LARGO, " +
					"A.C_TITULO, A.C_AVISO, A.D_FECHA_INICIO, A.D_FECHA_FIN, A.D_FECHA_CREACION " +
					"FROM TAB_AVISO A " +
					"INNER JOIN TAB_ESTADO ES ON ES.N_CODIGO = A.N_ESTADO " +
					"INNER JOIN TAB_TIPO TI ON TI.N_CODIGO = A.N_UBICACION";
			oConnection = ConexionBD.obtenerConexion();
			oStatement = oConnection.prepareStatement(query);
			oSet = oStatement.executeQuery();
			while (oSet.next()) {
				o = new Aviso();
				o.setCodigo(oSet.getInt(1));
				o.setUsuario(new Usuario(oSet.getInt(2)));
				o.setEstado(new Estado(oSet.getInt(3), oSet.getString(4), oSet.getString(5)));
				o.setUbicacion(new Tipo(oSet.getInt(6), oSet.getString(7), oSet.getString(8)));
				o.setTitulo(oSet.getString(9));
				o.setAviso(oSet.getString(10));
				o.setFechaInicio(oSet.getDate(11));
				o.setFechaFin(oSet.getDate(12));
				o.setFechaCreacion(oSet.getDate(13));
				lista.add(o);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(oSet);
			this.cerrarStatement(oStatement);
			this.cerrarConexion(oConnection);
		}
		return lista;
	}
	
}
